package servlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import conversion.DBDataSet;
import dataobjects.DeleteQuery;
import dataobjects.DislikeQuery;
import dataobjects.IndifferentQuery;
import dataobjects.LikeQuery;
import dataobjects.Message;
import dataobjects.ReportQuery;

public class ConnectionHelper {

	private static String connectionString = "java:comp/env/jdbc/AndroidDB";

	public static DBDataSet selectQuery(String query) {
		Context ctx;
		DBDataSet newset = null;
		Connection c = null;
		List rows = new ArrayList();
		ResultSet set = null;

		try {
			ctx = new InitialContext();

			if (ctx != null) {
				DataSource ds = (DataSource) ctx.lookup(connectionString);
				c = ds.getConnection();
				set = c.createStatement().executeQuery(query);

				ResultSetMetaData setdata = set.getMetaData();

				// String[] columns = {"id", "categoryID", "message"};

				String[] columns = new String[setdata.getColumnCount()];
				for (int i = 0; i < setdata.getColumnCount(); i++)
					columns[i] = setdata.getColumnName(i + 1);

				// List row = Arrays.asList( new Object[]{1,1, "something"})

				if (set.first()) {
					do {

						List row = new ArrayList();
						for (int i = 0; i < setdata.getColumnCount(); i++)
							row.add(set.getObject(i + 1));
						rows.add(row);
						if (set.isLast())
							break;
						else
							set.next();
					} while (true);
				}

				c.close();

				newset = new DBDataSet(rows, columns);

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			// out.println(e.getMessage());
			try {
				c.close();

				String[] columns = { "id", "categoryID", "message" , "nickname",  "likes", "dislikes", "indifferent"};
				List row = Arrays.asList(new Object[] { 1, 1, e.getMessage(), e.toString(), 0l, 0l, 0l });
				rows.add(row);
				newset = new DBDataSet(rows, columns);

			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				// e1.printStackTrace();
			}
		}

		return newset;
	}

	public static long insertSuggestion(Message m) {

		Context ctx;
		DBDataSet newset = null;
		Connection con = null;
		List rows = new ArrayList();
		ResultSet set = null;
		long insertId = -1;
		long latitude = m.getLatitude() == null ? 0l : m.getLatitude();
		long longitude = m.getLongitude() == null ? 0l : m.getLongitude();
		try {
			ctx = new InitialContext();
			if (ctx != null) {
				DataSource ds;

				ds = (DataSource) ctx.lookup(connectionString);

				con = ds.getConnection();

				PreparedStatement insertSuggestion = con
						.prepareStatement("insert into suggestions (nickname, message, categoryID, datePosted, latitude, longitude) values(?,?,?,?,?,?)");
				insertSuggestion.setString(1, m.getNickname());
				insertSuggestion.setString(2, m.getMessage());
				insertSuggestion.setLong(3, m.getCategoryID());
				insertSuggestion.setLong(4, m.getDatePosted());

				insertSuggestion.setLong(5, latitude);
				insertSuggestion.setLong(6, longitude);

				insertSuggestion.executeUpdate();

				PreparedStatement getLastInsertId = con
						.prepareStatement("SELECT LAST_INSERT_ID()");
				ResultSet rs = getLastInsertId.executeQuery();

				if (rs.first()) {
					insertId = rs.getLong("last_insert_id()");
				}
			}
		} catch (Exception e) {

			m.setMessage( e.getMessage());
			
		} 	
		try {
			
			con.close();
		} catch (SQLException e) {
			String message = m.getMessage()==null? "": m.getMessage();
			message = message +'\n';
			message = message + e.getMessage();
			m.setMessage( message );
			
		}
		return insertId;
	}

	public static boolean likeSuggestion(LikeQuery obj) {
		Context ctx;
		Connection con = null;
		boolean ret = false;

		try {
			ctx = new InitialContext();

	    if(ctx != null )
	    	{
		    	DataSource ds;
				
				ds = (DataSource)ctx.lookup(connectionString);
				
		    	con =  ds.getConnection();
		
		    	PreparedStatement insertSuggestion = con.prepareStatement("INSERT INTO votes (id, positiveVotes, negativeVotes, indifferentVotes) VALUES (?, 1, 0, 0) ON DUPLICATE KEY UPDATE positiveVotes = positiveVotes + 1;");
		    	insertSuggestion.setLong(1, obj.getId());
		    	
		    	insertSuggestion.execute();
		    	ret = true;
		    	con.close();
		}
		}catch (NamingException e) {
			

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return ret;
	}

	public static boolean dislikeSuggestion(DislikeQuery obj) {
		Context ctx;
		Connection con = null;
		boolean ret = false;

		try {
			ctx = new InitialContext();

	    if(ctx != null )
	    	{
		    	DataSource ds;
				
				ds = (DataSource)ctx.lookup(connectionString);
				
		    	con =  ds.getConnection();
		
		    	PreparedStatement insertSuggestion = con.prepareStatement("INSERT INTO votes (id, positiveVotes, negativeVotes, indifferentVotes) VALUES (?, 0, 1, 0) ON DUPLICATE KEY UPDATE negativeVotes = negativeVotes + 1;");
		    	insertSuggestion.setLong(1, obj.getId());
		    	
		    	insertSuggestion.execute();
		    	ret = true;
		    	con.close();
		 
		}
		}catch (NamingException e) {
			

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return ret;
	}

	public static boolean indifferentSuggestion(IndifferentQuery obj) {
		Context ctx;
		Connection con = null;
		boolean ret = false;

		try {
			ctx = new InitialContext();

	    if(ctx != null )
	    	{
		    	DataSource ds;
				
				ds = (DataSource)ctx.lookup(connectionString);
				
		    	con =  ds.getConnection();
		
		    	PreparedStatement insertSuggestion = con.prepareStatement("INSERT INTO votes (id, positiveVotes, negativeVotes, indifferentVotes) VALUES (?, 0, 0, 1) ON DUPLICATE KEY UPDATE indifferentVotes = indifferentVotes + 1;");
		    	insertSuggestion.setLong(1, obj.getId());
		    	
		    	insertSuggestion.execute();
		    	ret = true;
		    	con.close();
		 
		}
		}catch (NamingException e) {
			

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return ret;
	}

	public static Object delete(DeleteQuery obj) {
		Context ctx;
		Connection con = null;
		boolean ret = false;

		try {
			ctx = new InitialContext();

	    if(ctx != null )
	    	{
		    	DataSource ds;
				
				ds = (DataSource)ctx.lookup(connectionString);
				
		    	con =  ds.getConnection();
		
		    	PreparedStatement deleteVotes = con.prepareStatement("delete from votes where id=?");
		    	deleteVotes.setLong(1, obj.getMessageId());
		    	deleteVotes.execute();

		    	PreparedStatement deleteSuggestion = con.prepareStatement("delete from suggestions where id=?");
		    	deleteSuggestion.setLong(1, obj.getMessageId());
		    	deleteSuggestion.execute();
		    	
		    	ret = true;
		    	con.close();
		 
		}
		}catch (NamingException e) {
			

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return ret;
	}
	
	
	public static boolean reportSuggestion(ReportQuery obj) {
		Context ctx;
		Connection con = null;
		boolean ret = false;

		try {
			ctx = new InitialContext();

	    if(ctx != null )
	    	{
		    	DataSource ds;
				
				ds = (DataSource)ctx.lookup(connectionString);
				
		    	con =  ds.getConnection();
		
		    	PreparedStatement insertSuggestion = con.prepareStatement("INSERT INTO reports (id, aggregateReport, updateTime) values (?, 1, unix_timestamp()) ON DUPLICATE KEY UPDATE aggregateReport = aggregateReport + 1, updateTime = unix_timestamp();");
		    	insertSuggestion.setLong(1, obj.getId());
		    	
		    	insertSuggestion.execute();
		    	ret = true;
		    	con.close();
		}
		}catch (NamingException e) {
			

			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return ret;
	}

		
	
}
